<?php
///
///  composer 包
///  "phpoffice/phpspreadsheet": "^1.12",
///


namespace export;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

/**
 * 导出excel类
 * Class ExportExcel
 */
class ExportExcel
{


    /**
     * 导出excel表格
     * @return mixed
     */
    public function export()
    {
        //查询数据
        $orderList = [];

        set_time_limit(0);
        if (!empty($orderList)) {
            foreach ($orderList as $value1) {
                $export[] = [
                    $value1['app_name'],
                    $value1['uid'],
                    $value1['phone'],
                    $value1['task_id'],
                    $value1['status'],
                    $value1['info'],
                    $value1['create_time'],
                    $value1['submit_time'],
                    $value1['examine_time'],
                    $value1['overdue_time'],
                    $value1['imgs'],
                ];
            }
        }

        $newExcel = new Spreadsheet();             //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();   //获取当前操作sheet的对象
        $objSheet->setTitle('做单数据表');             //设置当前sheet的标题

        // 将默认字体设置为Arial
        $newExcel->getDefaultStyle()->getFont()->setName('微软雅黑');
        // 将字体大小设置为12
        $newExcel->getDefaultStyle()->getFont()->setSize(12);
        // 将D列单元格设置为粗体字
        $newExcel->getActiveSheet()->getStyle('H')->getFont()->setBold(true);

        // 设置默认行高为60
        $newExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(60);

        // 给标题单元格设置水平垂直居中对齐
        $newExcel->getActiveSheet()->getStyle('A:K')->getAlignment()->setHorizontal('center');
        $newExcel->getActiveSheet()->getStyle('A:K')->getAlignment()->setVertical('center');

        // 给每列单元格设置宽度
        $newExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
        $newExcel->getActiveSheet()->getColumnDimension('B')->setWidth(5);
        $newExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
        $newExcel->getActiveSheet()->getColumnDimension('D')->setWidth(5);
        $newExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
        $newExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);//自动宽度

        //设置第一栏的标题
        $objSheet->setCellValue('A1', 'app名称')
            ->setCellValue('B1', '用户id')
            ->setCellValue('C1', '用户手机')
            ->setCellValue('D1', '任务id')
            ->setCellValue('E1', '状态')
            ->setCellValue('F1', '用户提交信息')
            ->setCellValue('G1', '创建时间')
            ->setCellValue('H1', '提交时间')
            ->setCellValue('I1', '审核时间')
            ->setCellValue('J1', '过期时间')
            ->setCellValue('K1', '图片');

        // 第二行起，每一行的值，setCellValueExplicit是用来导出文本格式的
        foreach ($orderList as $key => $val) {
            $key = $key + 2;
            $objSheet->setCellValue('A' . $key, $val['app_name'])
                ->setCellValue('B' . $key, $val['uid'])
                ->setCellValue('C' . $key, $val['phone'])
                ->setCellValue('D' . $key, $val['task_id'])
                ->setCellValue('E' . $key, $val['status'])
                ->setCellValue('F' . $key, $val['info'])
                ->setCellValue('G' . $key, $val['create_time'])
                ->setCellValue('H' . $key, $val['submit_time'])
                ->setCellValue('I' . $key, $val['examine_time'])
                ->setCellValue('J' . $key, $val['overdue_time']);

            $num = 10;
            // 多图导出
            if (!empty($val['imgs'])) {
                $images = $val['imgs'];
                foreach ($images as $k => $v){
                    $image = $v;
                    // 获取本地文件夹路径
                    $dir = public_path().parse_url($v)['path'];
                    $file_info = pathinfo($image);
                    // 过滤非文件类型
                    if (!empty($file_info['basename'])) {
                        if (!file_exists($dir)){//判断图片是否存在
                            continue;
                        }
                        // 引入操作图片类
                        $drawings[$key] = new Drawing();
                        $drawings[$key]->setResizeProportional(false); // TODO 此处顺序不可调，因为导出默认是按原图像缩放的，设置成false才可以设置成可控制的宽度，要注意哦！
                        $drawings[$key]->setName('用户提交图片');
                        $drawings[$key]->setDescription('用户提交图片');
                        $drawings[$key]->setPath($dir);//图片路径
                        $drawings[$key]->setWidth(60);//图片宽度
                        $drawings[$key]->setHeight(60);//图片高度
                        $drawings[$key]->setOffsetX($num);
                        $drawings[$key]->setOffsetY(10);
                        $drawings[$key]->setCoordinates('K' . $key);
                        $drawings[$key]->setWorksheet($objSheet);
                    }
                    $num = $num + 70; // 增加每张图之间的间距
                }
            } else {
                $objSheet->setCellValue('K' . $key, '');
            }
        }
        return self::excelSave($newExcel, '做单数据表'.date('YmdHis', time()), 'Xls');//保存文件，返回文件下载地址
    }

    /**
     * 保存excel文件
     * @param $newExcel
     * @param $fileName
     * @param $suffix
     * @return mixed
     */
    public function excelSave($newExcel, $fileName, $suffix)
    {
        // 重命名表（UTF8编码不需要这一步）
        if (mb_detect_encoding($fileName) != "UTF-8") {
            $fileName = iconv("utf-8", "gbk//IGNORE", $fileName);
        }
        if ($suffix == 'xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            $class = "\PhpOffice\PhpSpreadsheet\Writer\Xlsx";
        } elseif ($suffix == 'xls') {
            header('Content-Type:application/vnd.ms-excel');
            $class = "\PhpOffice\PhpSpreadsheet\Writer\Xls";
        }
        $writer = new Xlsx($newExcel);//输出excel类
        $path = public_path().'/'.$fileName.'.'.$suffix;//文件保存路径
        $writer->save($path);//保存文件
        // 删除清空 释放内存
        $newExcel->disconnectWorksheets();
        unset($spreadsheet);
        $protocol = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off' || $_SERVER['SERVER_PORT'] == 443) ? "https://" : "http://";
        $domainName = $_SERVER['HTTP_HOST'];
        return app('json')->success([$protocol.$domainName.$path]);//返回下载地址
    }

}
